Snowflakeの3種類のTIMESTAMPの違いをまとめてみた #SnowflakeDB
さがらです。
SnowflakeではTIMESTAMPに関して3種類の型があります。その3種の違いを本記事でまとめてみます。
Snowflakeにおける3種類のTIMESTAMP
Snowflakeでは、以下3種類のTIMESTAMPがあります。
- TIMESTAMP_LTZ
- TIMESTAMP_NTZ
- TIMESTAMP_TZ
それぞれの説明は公式Docにも載っていますが、パッと見ただけでは違いがわからないと思います。(私も最初はわかりませんでした。笑)
そこで、次章から公式Docのサンプルコードに沿って、それぞれの違いをまとめていきます。
TIMESTAMP_LTZ
まず1つ目、TIMESTAMP_LTZ
についてです。
簡単に言うと、timezoneセッションパラメーターの値に応じて、タイムゾーンが動的に変化するTIMESTAMPです。
まず下記のコードを実行して、timezone
パラメータをAmerica/Los_Angeles
にして、テーブル作成とレコードのinsertを行います。
create or replace table ts_test(ts timestamp_ltz); alter session set timezone = 'America/Los_Angeles'; insert into ts_test values('2014-01-01 16:00:00'); insert into ts_test values('2014-01-02 16:00:00 +00:00');
この上で、以下のSELECT文を実行してみます。
select ts, hour(ts) from ts_test;
1行目2014-01-01 16:00:00
としてタイムゾーンを指定せずINSERTしたレコードには、America/Los_Angeles
のタイムゾーン「-0800 (UTC -8h)」が追記されて登録されました。
2行目2014-01-02 16:00:00 +00:00
としてタイムゾーンを指定してINSERTしたレコードは、America/Los_Angeles
のタイムゾーン「-0800 (UTC -8h)」に変換された値が登録されました。
続けて、timezone
パラメータをAmerica/New_York
にして、先ほどと同じSELECT文を実行してみます。
alter session set timezone = 'America/New_York'; select ts, hour(ts) from ts_test;
すると、1行目も2行目もAmerica/New_York
のタイムゾーン「-0500 (UTC -5h)」に変換された値が表示されました。
このように、timezoneセッションパラメーターの値に応じて値が動的に変化するのが、TIMESTAMP_LTZです。
TIMESTAMP_NTZ
2つ目、TIMESTAMP_NTZ
についてです。
簡単に言うと、レコード登録時のタイムゾーンもtimezoneセッションパラメータも、どちらの影響も受けないTIMESTAMPです。
まず下記のコードを実行して、timezone
パラメータをAmerica/Los_Angeles
にして、テーブル作成とレコードのinsertを行います。
create or replace table ts_test(ts timestamp_ntz); alter session set timezone = 'America/Los_Angeles'; insert into ts_test values('2014-01-01 16:00:00'); insert into ts_test values('2014-01-02 16:00:00 +00:00');
この上で、以下のSELECT文を実行してみます。
select ts, hour(ts) from ts_test;
下図がこのSELECT文の実行結果ですが、1行目はタイムゾーンを含まない値、2行目はタイムゾーンを含む値をINSERTしましたが、どちらもタイムゾーンがなくなった形で表示されているのがわかると思います。timezone
パラメータのAmerica/Los_Angeles
の影響も特にありません。
このように、レコード登録時のタイムゾーンもtimezoneセッションパラメータも、どちらの影響も受けないのがTIMESTAMP_NTZです。
TIMESTAMP_TZ
3つ目、TIMESTAMP_TZ
についてです。
簡単に言うと、値としてはタイムゾーンを必ず持つが、timezoneセッションパラメーターの影響は値の登録時しか受けないTIMESTAMPです。
まず下記のコードを実行して、timezone
パラメータをAmerica/Los_Angeles
にして、テーブル作成とレコードのinsertを行います。
create or replace table ts_test(ts timestamp_tz); alter session set timezone = 'America/Los_Angeles'; insert into ts_test values('2014-01-01 16:00:00'); insert into ts_test values('2014-01-02 16:00:00 +00:00');
この上で、以下のSELECT文を実行してみます。
select ts, hour(ts) from ts_test;
下図がこのSELECT文の実行結果となります。
1行目2014-01-01 16:00:00
としてタイムゾーンを指定せずINSERTしたレコードには、America/Los_Angeles
のタイムゾーン「-0800 (UTC -8h)」が追記されました。
2行目2014-01-02 16:00:00 +00:00
としてタイムゾーンを指定してINSERTしたレコードは、INSERT時の値のタイムゾーンのまま「+0000」で登録されました。
続けて、timezone
パラメータをAmerica/New_York
にして、先ほどと同じSELECT文を実行してみます。
alter session set timezone = 'America/New_York'; select ts, hour(ts) from ts_test;
すると、1行目も2行目も値が変化していないことがわかります。timezone
パラメータの値を変えることで、TIMESTAMP_LTZ
の場合は値のタイムゾーンも変化していていたのが、TIMESTAMP_TZ
では値のタイムゾーンが変化しませんでした。
このように、値としてはタイムゾーンを必ず持つが、timezoneセッションパラメーターの影響は値の登録時しか受けないのがTIMESTAMP_TZです。
3種類のTIMESTAMPの使い分け
ここで、3種類のTIMESTAMPの使い分けについてまとめてみます。
- TIMESTAMP_NTZ ※デフォルト値
- タイムゾーンを特に意識せずに、日時データを保持しておきたい場合
- ※参考:Snowflakeには
DATETIME
という型もありますが、これはTIMESTAMP_NTZのエイリアスです
- TIMESTAMP_TZ
- タイムゾーンまで値を保持した上で、クエリを発行するユーザーのタイムゾーンごとに値を変更させたくない場合
- TIMESTAMP_LTZ
- タイムゾーンまで値を保持した上で、クエリを発行するユーザーのタイムゾーンに合わせて値を動的に変更させたい場合
おまけ:「TIMESTAMP」型について
実は、SnowflakeではTIMESTAMPという型もあります。
なのですが、TIMESTAMPはこれまでに説明した3種類のTIMESTAMP_*のエイリアスで、デフォルトではTIMESTAMP_NTZを意味しています。
もし、TIMESTAMPで別のTIMESTAMP_*を参照させたい場合は、TIMESTAMP_TYPE_MAPPING
というセッションパラメーターをを変更すればOKです。
※対象のセッション中TIMESTAMP
型を指定したときにTIMESTAMP_TZ
が使われるように変更するためのクエリを以下に記しておきます。
alter session set TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_TZ'; show parameters like 'TIMESTAMP_TYPE_MAPPING';
最後に
Snowflakeでの3種類のTIMESTAMPの違いをまとめてみました。
データを管理する上で日時は欠かせない要素ですので、この知識を抑えておくといざというときに困惑せずに済むはずです。